package com.smartleanx.module.hrm.framework.excelUtil;

import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import com.smartleanx.module.hrm.framework.excelUtil.handler.CustomCellDropDownHandler;
import com.smartleanx.module.hrm.framework.excelUtil.handler.CustomDateValidateHandler;
import org.apache.commons.codec.CharEncoding;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.http.HttpHeaders;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;


public class ExcelUtils {

    /**
     * 读 Excel
     */
    public static <T> List<T> synchronousRead(Class<T> cls, MultipartFile file) throws IOException {
        return EasyExcel.read(file.getInputStream()).head(cls).sheet().doReadSync();
    }

    /**
     * 标准数据导出
     * 
     * @param data 数据
     * @param dataClass 表头
     * @param response ..
     */
    public static void export(Collection<?> data, Class dataClass, HttpServletResponse response) {
        export(data, dataClass, response, "导出数据");
    }

    /**
     * 标准数据导出
     * 
     * @param data 数据
     * @param dataClass 表头
     * @param response ..
     * @param sheetName 自定义第一个sheet名称
     */
    public static void export(Collection<?> data, Class dataClass, HttpServletResponse response, String sheetName) {
        try {
            String fileName = URLEncoder.encode("数据导出.xlsx", CharEncoding.UTF_8);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding(CharEncoding.UTF_8);
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + fileName);
            EasyExcel.write(response.getOutputStream(), dataClass)
                // sheet名
                .sheet(sheetName)
                // 导出
                .doWrite(data);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导入模板标准方法
     * 
     * @param response ..
     * @param headClass 表头
     * @param dropDownData 模板选项数据
     * @param templateData 模板示例数据
     */
    public static void exportTemplate(HttpServletResponse response, List<List<String>> headClass,
        Map<String, List<String>> dropDownData, List templateData) {

        try {
            // 通用内容设置
            String fileName = URLEncoder.encode("钉钉导入模板.xlsx", CharEncoding.UTF_8);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding(CharEncoding.UTF_8);
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + fileName);

            // 构建模板数据
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

            // 导入模板
            WriteSheet writeSheet = EasyExcel.writerSheet(0, "钉钉导入模板")
                // 自定义Handler，处理下拉数据
                .registerWriteHandler(new CustomCellDropDownHandler(dropDownData))
                // 自定义Handler，校验日期格式
//                .registerWriteHandler(new CustomDateValidateHandler(dateValidateColumn))
                // 表头
                .head(headClass).build();
            excelWriter.write(templateData, writeSheet);

            // 下拉选项隐藏Sheet
            Workbook workbook =
                excelWriter.writeContext().writeSheetHolder().getParentWriteWorkbookHolder().getWorkbook();
            if (dropDownData != null) {
                int sheetNo = 1;
                for (String key : dropDownData.keySet()) {
                    List<String> strings = dropDownData.get(key);
                    if (strings.size() < CustomCellDropDownHandler.LIMIT) {
                        continue;
                    }
                    List<HiddenColumn> sheetData = new ArrayList<>();
                    for (String s : strings) {
                        sheetData.add(new HiddenColumn(s));
                    }
                    // 下拉sheet
                    WriteSheet dropSheet = EasyExcel.writerSheet(sheetNo, key)
                        // 设置并隐藏表头
                        .head(HiddenColumn.class).needHead(false).build();
                    excelWriter.write(sheetData, dropSheet);
                    // 隐藏辅助Sheet
                    workbook.setSheetHidden(sheetNo, true);

                    sheetNo++;
                }
            }

            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void exportInclude(Collection<?> data, Class dataClass, Collection<String> includeColumnFieldNames,
        HttpServletResponse response) {
        try {
            String fileName = URLEncoder.encode("数据导出.xlsx", CharEncoding.UTF_8);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding(CharEncoding.UTF_8);
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + fileName);
            EasyExcel.write(response.getOutputStream(), dataClass)
                // 包含字段
                .includeColumnFieldNames(includeColumnFieldNames)
                // sheet名
                .sheet("数据")
                // 导出
                .doWrite(data);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void exportExclude(Collection<?> data, Class dataClass, Collection<String> excludeColumnFieldNames,
        HttpServletResponse response) {
        try {
            String fileName = URLEncoder.encode("数据导出.xlsx", CharEncoding.UTF_8);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding(CharEncoding.UTF_8);
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + fileName);
            EasyExcel.write(response.getOutputStream(), dataClass)
                // 排除字段
                .excludeColumnFieldNames(excludeColumnFieldNames)
                // sheet名
                .sheet("数据")
                // 导出
                .doWrite(data);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void exportWithWriteHandler(Collection<?> data, Class dataClass, WriteHandler writeHandler,
        HttpServletResponse response) {
        try {
            String fileName = URLEncoder.encode("数据导出.xlsx", CharEncoding.UTF_8);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding(CharEncoding.UTF_8);
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + fileName);
            EasyExcel.write(response.getOutputStream(), dataClass)
                // 内存中处理
                .inMemory(Boolean.TRUE)
                // 注册自定义WriteHandler
                .registerWriteHandler(writeHandler)
                // sheet名
                .sheet("数据")
                // 导出
                .doWrite(data);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 表头增加批注方法
     */
    public static void addComment(Sheet sheet, String text, int row, int cell, int col1, int row1, int col2, int row2) {
        Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
        Comment comment = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2));
        comment.setString(new XSSFRichTextString(text));
        sheet.getRow(row).getCell(cell).setCellComment(comment);
    }
}
